package include.excel_export;

import jxl.*;
import jxl.write.*;
import java.io.*;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import include.nseer_db.nseer_db;
import java.sql.*;

public class ExcelWriter {

    private String dbase = "";
    private String tablename = "";
    private nseer_db erp_db;

    public void CreExcel(String dbase, String tablename, String tablenick, String[] cols, String file_path) {
        this.dbase = dbase;
        this.tablename = tablename;
        erp_db = new nseer_db(dbase);
        String targetfile = file_path;
        String worksheet = tablenick;
        WritableWorkbook workbook;
        try {
            OutputStream os = new FileOutputStream(targetfile);
            workbook = Workbook.createWorkbook(os);
            WritableSheet sheet = workbook.createSheet(worksheet, 0);
            jxl.write.Label label;
            List excel_col_name = (List) new java.util.ArrayList();
            List excel_col_type = (List) new java.util.ArrayList();
            List excel_col_nick = (List) new java.util.ArrayList();
            for (int i = 0; i < cols.length; i++) {
                excel_col_name.add(cols[i].split("⊙")[1]);
                excel_col_type.add(cols[i].split("⊙")[0]);
                excel_col_nick.add(cols[i].split("⊙")[2]);
            }
            for (int i = 0; i < excel_col_nick.size(); i++) {
                String col_nick = (String) excel_col_nick.get(i);
                label = new jxl.write.Label(i, 0, col_nick);
                sheet.addCell(label);
            }
            String sql = "select * from " + tablename;
            ResultSet rs = erp_db.executeQuery(sql);
            List data = (List) new java.util.ArrayList();
            int n = 1;
            while (rs.next()) {
                for (int i = 0; i < excel_col_name.size(); i++) {
                    String col_name = (String) excel_col_name.get(i);
                    String col_type = (String) excel_col_type.get(i);
                    if (col_type.indexOf("text") != -1 || col_type.indexOf("mediumtext") != -1 || col_type.indexOf("varchar") != -1) {
                        label = new jxl.write.Label(i, n, rs.getString(col_name));
                        sheet.addCell(label);
                    } else if (col_type.indexOf("int") != -1) {
                        jxl.write.Number number = new jxl.write.Number(i, n, rs.getInt(col_name));
                        sheet.addCell(number);
                    } else if (col_type.indexOf("double") != -1 || col_type.indexOf("decimal") != -1) {
                        jxl.write.Number number = new jxl.write.Number(i, n, rs.getDouble(col_name));
                        sheet.addCell(number);
                    } else if (col_type.indexOf("datetime") != -1) {
                        Format format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        Date date = new Date();
                        Date date1 = (Date) format.parseObject(rs.getString(col_name));
                        jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd HH:mm:ss");
                        jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
                        jxl.write.DateTime labelDT = new jxl.write.DateTime(i, n, date1, wcfDF);
                        sheet.addCell(labelDT);
                    } else {
                        label = new jxl.write.Label(i, n, rs.getString(col_name));
                        sheet.addCell(label);
                    }
                }
                n++;
            }
            erp_db.close();
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void CreExcel(String dbase, String tablename, String tablenick, String[] cols, String file_path, String condition, String queue) {
        this.dbase = dbase;
        this.tablename = tablename;
        erp_db = new nseer_db(dbase);
        String targetfile = file_path;
        String worksheet = tablenick;
        WritableWorkbook workbook;
        try {
            OutputStream os = new FileOutputStream(targetfile);
            workbook = Workbook.createWorkbook(os);
            WritableSheet sheet = workbook.createSheet(worksheet, 0);
            jxl.write.Label label;
            List excel_col_name = (List) new java.util.ArrayList();
            List excel_col_type = (List) new java.util.ArrayList();
            List excel_col_nick = (List) new java.util.ArrayList();
            for (int i = 0; i < cols.length; i++) {
                excel_col_name.add(cols[i].split("⊙")[1]);
                excel_col_type.add(cols[i].split("⊙")[0]);
                excel_col_nick.add(cols[i].split("⊙")[2]);
            }
            for (int i = 0; i < excel_col_nick.size(); i++) {
                String col_nick = (String) excel_col_nick.get(i);
                label = new jxl.write.Label(i, 0, col_nick);
                sheet.addCell(label);
            }
            String sql = "select * from " + tablename + " " + condition + " " + queue;
            ResultSet rs = erp_db.executeQuery(sql);
            List data = (List) new java.util.ArrayList();
            int n = 1;
            while (rs.next()) {
                for (int i = 0; i < excel_col_name.size(); i++) {
                    String col_name = (String) excel_col_name.get(i);
                    String col_type = (String) excel_col_type.get(i);
                    if (col_type.indexOf("text") != -1 || col_type.indexOf("mediumtext") != -1 || col_type.indexOf("varchar") != -1) {
                        label = new jxl.write.Label(i, n, rs.getString(col_name));
                        sheet.addCell(label);
                    } else if (col_type.indexOf("int") != -1) {
                        jxl.write.Number number = new jxl.write.Number(i, n, rs.getInt(col_name));
                        sheet.addCell(number);
                    } else if (col_type.indexOf("double") != -1 || col_type.indexOf("decimal") != -1) {
                        jxl.write.Number number = new jxl.write.Number(i, n, rs.getDouble(col_name));
                        sheet.addCell(number);
                    } else if (col_type.indexOf("datetime") != -1) {
                        Format format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        Date date = new Date();
                        Date date1 = (Date) format.parseObject(rs.getString(col_name));
                        jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd HH:mm:ss");
                        jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
                        jxl.write.DateTime labelDT = new jxl.write.DateTime(i, n, date1, wcfDF);
                        sheet.addCell(labelDT);
                    } else {
                        label = new jxl.write.Label(i, n, rs.getString(col_name));
                        sheet.addCell(label);
                    }
                }
                n++;
            }
            erp_db.close();
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        String targetfile = "c:/out.xls";
        String worksheet = "List";
        String[] title = {"ID", "NAME", "DESCRIB"};
        WritableWorkbook workbook;
        try {
            OutputStream os = new FileOutputStream(targetfile);
            workbook = Workbook.createWorkbook(os);
            WritableSheet sheet = workbook.createSheet(worksheet, 0);
            jxl.write.Label label;
            for (int i = 0; i < title.length; i++) {
                for (int j = 0; j < 50; j++) {
                    label = new jxl.write.Label(i, j, title[i]);
                    sheet.addCell(label);
                }
            }
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
} 